﻿Imports FastReport.Report
Imports MySql.Data.MySqlClient
Imports System.Data.SQLite
Public Class frmRptDiag
    Dim Strsql As String
    Dim _mysql As New ClsSQLhelper
    Dim _sqlite As New ClsSQLite

    Public Report_Name As String
    Public Primary_Key As Integer
    Public HN_Criteria As String
    Private Sub frmRptDiag_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try



            'Strsql = "SELECT m_person.ROWID,m_person.PCUCODE,m_person.CID,m_person.PID,m_person.HID,m_person.PRENAME, "
            'Strsql = Strsql & " m_person.NAME,m_person.LNAME,m_person.HN,m_person.SEX,m_person.BIRTH,m_person.HOUSE,m_person.VILLAGE,"
            'Strsql = Strsql & " m_person.TAMBON,m_person.AMPUR,m_person.CHANGWAT,m_person.MSTATUS,m_person.OCCUPA,m_person.RACE,"
            'Strsql = Strsql & " m_person.NATION,m_person.RELIGION,m_person.EDUCATE,m_person.FSTATUS,m_person.FATHER,m_person.MOTHER,"
            'Strsql = Strsql & " m_person.COUPLE,m_person.MOVEIN,m_person.DISCHAR,m_person.DDISCH,m_person.BGROUP,m_person.LABOR,"
            'Strsql = Strsql & " m_person.VHID,m_person.TYPEAREA,m_person.D_UPDATE,m_person.FILENAME,m_person.`STATUS`,m_person.PERSON_USE,"
            'Strsql = Strsql & " m_diag.DATE_SERV,m_diag.DIAGTYPE,m_diag.DIAGCODE,m_diag.CODE_ID ,m_diag.D_UPDATE,m_diag.CLINIC,l_error_code.`EXPLAIN`,l_error_code.REMARK"
            'Strsql = Strsql & " FROM m_person left join m_diag  on m_diag.pid = m_person.pid left join l_error_code on m_diag.CODE_ID = l_error_code.CODE_ID"
            'Strsql = Strsql & " where m_diag.CODE_ID <>'' and cid <> '0000000000000' and cid <> '' order by hn,m_diag.DIAGTYPE"

            Strsql = "SELECT concat(right(BIRTH,2) ,'-',mid(BIRTH,5,2) ,'-',left(BIRTH,4)+543)  as BIRTHDay ,m_person.*,l_error_code.`EXPLAIN`,l_error_code.REMARK ,right(m_diag.PID,7) AS HN,m_diag.SEQ,"
            Strsql = Strsql & " concat(right(DATE_SERV,2) ,'-',mid(DATE_SERV,5,2) ,'-',left(DATE_SERV,4)+543)  as vstdate,"
            Strsql = Strsql & " m_diag.DIAGTYPE,m_person.SEX,"
            Strsql = Strsql & " m_diag.DIAGCODE,"
            Strsql = Strsql & " m_diag.CLINIC,"
            Strsql = Strsql & " l_clinic.CODE_D,"
            Strsql = Strsql & " m_diag.CODE_ID as DiagErr"
            Strsql = Strsql & " from m_diag "
            Strsql = Strsql & " left join l_clinic on left(m_diag.CLINIC,3) = l_clinic.CLINIC_CODE"
            Strsql = Strsql & " left join m_person on m_diag.PID = m_person.PID"
            Strsql = Strsql & " left join l_error_code on Replace(left(m_diag.CODE_ID,7),',','') = l_error_code.CODE_ID "
            Strsql = Strsql & " where m_diag.CODE_ID <> 'DX9200'   and cid <> '' and cid <> '0000000000000'"
            Strsql = Strsql & " order by HN,DATE_SERV,DIAGTYPE"


            ''Set m_diag Dataset
            'Strsql = "Select * from m_diag where pid not in (select PID from  m_person) "
            'Dim DS_Diag As New DataSet
            'DS_Diag = _mysql.GetMYSQLDataset(Strsql, "Whis", "m_diag")


            ''Import Data to SQLite
            'Dim DA As New SQLiteDataAdapter
            'Dim SQLiteConnq As New SQLite.SQLiteConnection
            'Dim cmdBuilder As SQLiteCommandBuilder
            'Dim DT As New DataTable
            '' Strsql = "select hn,cid,pname,fname,lname,birthday,sex,bloodgrp,fathername,mathername,last_visit,nationality,type_area,occupation,religion,marrystatus,educate,last_update  from patient"
            'Strsql = "Select * from m_diag"
            'SQLiteConnq.ConnectionString = ClsSQLite.SQLiteConnectionStr
            'DA = New SQLiteDataAdapter(Strsql, SQLiteConnq)
            'cmdBuilder = New SQLiteCommandBuilder(DA)
            'DA.Fill(DT)
            'Dim dr As DataRow
            'For Each dr In DS_Diag.Tables("m_diag").Rows
            '    'DS2.Tables(0).ImportRow(dr)
            '    Dim DsNewRow As DataRow
            '    DsNewRow = DT.NewRow
            '    For i = 0 To DS_Diag.Tables("m_diag").Columns.Count - 1
            '        DsNewRow.Item(i) = dr.Item(i)
            '    Next
            '    DT.Rows.Add(DsNewRow)
            'Next
            'DA.Update(DT)


            'Dim HN_VIST_Criteria As String = ""
            'For i = 1 To DS_Diag.Tables(0).Rows.Count - 1
            '    HN_VIST_Criteria = HN_VIST_Criteria & " ( vn_stat.HN ='" & Mid(DS_Diag.Tables(0).Rows(i).Item("PID").ToString, 7, 7) & "' and vstdate = '" & Mid(DS_Diag.Tables(0).Rows(i).Item("DATE_SERV").ToString, 1, 4) & "-" & Mid(DS_Diag.Tables(0).Rows(i).Item("DATE_SERV").ToString, 5, 2) & "-" & Mid(DS_Diag.Tables(0).Rows(i).Item("DATE_SERV").ToString, 7, 2) & "') or "
            '    HN_Criteria = HN_Criteria & "'" & Mid(DS_Diag.Tables(0).Rows(i).Item("PID").ToString, 7, 7) & "',"
            'Next




            'If HN_VIST_Criteria <> Nothing Then
            '    HN_VIST_Criteria = Mid(HN_VIST_Criteria, 1, Len(HN_VIST_Criteria) - 3)
            '    HN_Criteria = Mid(HN_Criteria, 1, Len(HN_Criteria) - 1)
            'End If


            'Dim DT_VN_STAT As New DataTable
            'Strsql = "select * from vn_stat where " & HN_VIST_Criteria
            'DT_VN_STAT = _mysql.GetMYSQLDataTable(Strsql, "vn_stat", True)


            'Dim DT_VN_Patient As New DataTable
            'Strsql = "select * from patient where hn in ( " & HN_Criteria & ")"
            'DT_VN_Patient = _mysql.GetMYSQLDataTable(Strsql, "patient", True)





            ''Import VN_STAT Data to SQLite
            'DA = New SQLiteDataAdapter
            'SQLiteConnq = New SQLite.SQLiteConnection
            ''Dim cmdBuilder As SQLiteCommandBuilder
            ''Dim DT As New DataTable
            'Strsql = "Select * from vn_stat where " & HN_VIST_Criteria
            'SQLiteConnq.ConnectionString = ClsSQLite.SQLiteConnectionStr
            'DA = New SQLiteDataAdapter(Strsql, SQLiteConnq)
            'cmdBuilder = New SQLiteCommandBuilder(DA)
            'DA.Fill(DT)

            'For Each dr In DT_VN_STAT.Rows
            '    'DS2.Tables(0).ImportRow(dr)
            '    Dim DsNewRow As DataRow
            '    DsNewRow = DT.NewRow
            '    For i = 0 To DT_VN_STAT.Columns.Count - 1
            '        DsNewRow.Item(i) = dr.Item(i)
            '    Next
            '    DT.Rows.Add(DsNewRow)
            'Next
            'DA.Update(DT)




            ''Import Patient Data to SQLite
            'DA = New SQLiteDataAdapter
            'SQLiteConnq = New SQLite.SQLiteConnection
            ''Dim cmdBuilder As SQLiteCommandBuilder
            ''Dim DT As New DataTable
            'Strsql = "Select * from patient where hn in (" & HN_Criteria & ")"
            'SQLiteConnq.ConnectionString = ClsSQLite.SQLiteConnectionStr
            'DA = New SQLiteDataAdapter(Strsql, SQLiteConnq)
            'cmdBuilder = New SQLiteCommandBuilder(DA)
            'DA.Fill(DT)

            'For Each dr In DS_Diag.Tables("patient").Rows
            '    'DS2.Tables(0).ImportRow(dr)
            '    Dim DsNewRow As DataRow
            '    DsNewRow = DT.NewRow
            '    For i = 0 To DS_Diag.Tables("patient").Columns.Count - 1
            '        DsNewRow.Item(i) = dr.Item(i)
            '    Next
            '    DT.Rows.Add(DsNewRow)
            'Next
            'DA.Update(DT)




            ''Load Report Data
            Dim DS_Documents As New DataSet

            'Strsql = "SELECT vn_stat.hn,vn_stat.cid,vn_stat.vn,vn_stat.vstdate,"
            'Strsql = Strsql & " vn_stat.pdx,vn_stat.dx0,vn_stat.dx1,vn_stat.dx2,vn_stat.dx3,vn_stat.dx4,vn_stat.main_pdx,vn_stat.spclty,"
            'Strsql = Strsql & " spclty.name,patient.* from vn_stat  left join spclty on vn_stat.spclty = spclty.spclty "
            'Strsql = Strsql & " left join patient on vn_stat.hn = patient.hn where " & HN_VIST_Criteria & " order by vstdate"

            DS_Documents = _mysql.GetMYSQLDataset(Strsql, "IDM", "reporttemplate")

            If DS_Documents.Tables(0).Rows.Count = 0 Then
                MsgBox("ไม่พบข้อมูล")
                Exit Sub
            End If

            Dim DR_Doc As DataRow
            For Each DR_Doc In DS_Documents.Tables(0).Rows
                If DR_Doc("vstdate").ToString = "" Then

                End If

            Next


            Dim TotalRow As Integer = DS_Documents.Tables(0).Rows.Count
            Dim AddRow As Integer


            Dim report1 As New FastReport.Report

            report1.Preview = PreviewControl2
            report1.Load(Application.StartupPath & "/Reports/rptDiagCheck.frx")


            report1.AutoFillDataSet = True

            report1.RegisterData(DS_Documents)

            report1.GetDataSource("reporttemplate").Enabled = True




            report1.Refresh()
            report1.Preview = PreviewControl2
            report1.Show()
            ' report1.Design()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Try


            If BackgroundWorker1.CancellationPending = True Then
                e.Cancel = True
                Exit Sub
            Else
              
            End If

         
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged

    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

    End Sub
End Class